/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.obrock;

import android.database.Cursor;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

/**
 * @author Дмитрий Г. Кузнецов
 */
public class Account implements Serializable {

    public static final int TYPE_NORMAL = 0;
    public static final int TYPE_DEBT = 1;
    public static final int TYPE_CARD = 2;

    public long accountID = -1;
    public String name = "";
    public int type = TYPE_NORMAL;
    public Date date = new Date();
    //public Date last = new Date();
    public Double amount = 0.0;
    public String remark = "";
    public int flags = 0;
    public List<Analytics> analytics = new ArrayList<Analytics>(0);
    public double balance = 0.0;
    public double balanceDebt = 0.0;
    public double balanceCredit = 0.0;

    private static Database db = null;

    public Account(Database db, long id) {
        this(db);
        accountID = id;
        refresh();
    }

    public Account(Database db, String name) {
        this(db);
        this.name = name;
    }

    public Account(Database db) {
        this.db = db;
    }

    public void refresh() {
        Cursor cr = db.getReadableDatabase().rawQuery("SELECT "
                // 0   1     2     3     4       5       6
                + "id, name, type, date, amount, remark, flags "
                + "FROM accounts "
                + "WHERE id=?;",
                new String[]{"" + accountID});
        if (cr.moveToFirst()) {
            accountID = cr.getLong(0);
            name = cr.getString(1);
            type = cr.getInt(2);
            date = new Date(cr.getLong(3));
            amount = cr.getDouble(4);
            remark = cr.getString(5);
            flags = cr.getInt(6);
        }
        cr.close();

        List<Analytics> a = new ArrayList<Analytics>(0);
        cr = db.getReadableDatabase().rawQuery("SELECT id_analytics "
                + "FROM relates "
                + "WHERE id_account = ?",
                new String[]{"" + accountID});
        if (cr.moveToFirst()) {
            do {
                a.add(new Analytics(db, this, cr.getLong(0)));
            } while (cr.moveToNext());
        }
        cr.close();
        analytics = a;

        refreshBalance();
    }

    public void append() {
        db.getWritableDatabase().execSQL("INSERT INTO accounts "
                + "(name, type, date, amount, remark, flags) "
                + "VALUES (?,?,?,?,?,?);",
                new Object[]{name, type, date.getTime(), amount, remark, flags});
        accountID = db.getIdent("accounts");

        for (Analytics a : analytics) {
            link(a);
        }

        DropboxThread.dataChanged();
    }

    public void update() {
        db.getWritableDatabase().execSQL("UPDATE accounts SET "
                + "name=?, type=?, date=?, amount=?, remark=?, flags=? "
                + "WHERE id=?;",
                new Object[]{name, type, date.getTime(), amount, remark, flags, accountID});

        db.getWritableDatabase().execSQL("DELETE FROM relates "
                + "WHERE id_account = ? ",
                new Object[]{accountID});

        for (Analytics a : analytics) {
            link(a);
        }

        DropboxThread.dataChanged();
    }

    public void delete() {
        db.getWritableDatabase().execSQL("DELETE FROM accounts "
                + "WHERE id = ?;",
                new Object[]{accountID});
        db.getWritableDatabase().execSQL("DELETE FROM relates "
                + "WHERE id_account = ?;",
                new Object[]{accountID});
        db.getWritableDatabase().execSQL("DELETE FROM balance "
                + "WHERE id_from = ? "
                + "OR id_to = ?;",
                new Object[]{accountID, accountID});
        DropboxThread.dataChanged();
    }

    public double paymentsFrom(Calendar[] period) {
        double out = 0;
        Cursor cr = null;

        if (period != null) {
            cr = db.getReadableDatabase().rawQuery("SELECT "
                    + "SUM(amount), MAX(date) "
                    + "FROM balance "
                    + "WHERE mode = ? "
                    + "AND id_from=? " +
                    "AND date >= ? " +
                    "AND date <= ?",
                    new String[]{"" + Balance.MODE_NORMAL, "" + accountID,
                            "" + period[0].getTimeInMillis(), "" + period[1].getTimeInMillis(),});
        } else {
            cr = db.getReadableDatabase().rawQuery("SELECT "
                    + "SUM(amount), MAX(date) "
                    + "FROM balance "
                    + "WHERE mode = ? "
                    + "AND id_from=? ",
                    new String[]{"" + Balance.MODE_NORMAL, "" + accountID});
        }

        if (cr.moveToFirst()) {
            out = cr.getDouble(0);
        }
        return out;
    }

    public double paymentsTo(Calendar[] period) {
        double in = 0;

        Cursor cr = db.getReadableDatabase().rawQuery("SELECT "
                + "SUM(amount), MAX(date) "
                + "FROM balance "
                + "WHERE type <> ? "
                + "AND mode = ? "
                + "AND id_to=? " +
                "AND date >= ? " +
                "AND date <= ?",
                new String[]{"" + Balance.TYPE_COMPLEX, "" + Balance.MODE_NORMAL, "" + accountID,
                        "" + period[0].getTimeInMillis(), "" + period[1].getTimeInMillis()});
        if (cr.moveToFirst()) {
            in = cr.getDouble(0);
        }
        cr.close();
        cr = db.getReadableDatabase().rawQuery("SELECT "
                + "SUM(complex.amount), MAX(balance.date) "
                + "FROM balance, complex "
                + "WHERE balance.type = ? "
                + "AND balance.mode = ? "
                + "AND balance.id = complex.id_balance "
                + "AND complex.id_account = ? " +
                "AND balance.date >= ? " +
                "AND balance.date <= ?",
                new String[]{"" + Balance.TYPE_COMPLEX, "" + Balance.MODE_NORMAL, "" + accountID,
                        "" + period[0].getTimeInMillis(), "" + period[1].getTimeInMillis()});
        if (cr.moveToFirst()) {
            in += cr.getDouble(0);
        }
        cr.close();

        return in;
    }

    public double[] balanceAt(Calendar at) {
        double balance = 0.0;
        double balanceDebt = 0.0;
        double balanceCredit = 0.0;

        double out = 0;
        double in = 0;

        at.set(Calendar.HOUR,23);
        at.set(Calendar.MINUTE,59);
        at.set(Calendar.SECOND,59);

        Cursor cr = db.getReadableDatabase().rawQuery("SELECT "
                + "SUM(amount), MAX(date) "
                + "FROM balance "
                + "WHERE mode = ? "
                + "AND id_from = ? "
                + "AND date <= ?",
                new String[]{"" + Balance.MODE_NORMAL, "" + accountID, "" + at.getTimeInMillis()});
        if (cr.moveToFirst()) {
            out = cr.getDouble(0);
            Date d = new Date(cr.getLong(1));
            //if (out > 0 && last.before(d)) {
            //    last = d;
            //}
        }
        cr.close();
        cr = db.getReadableDatabase().rawQuery("SELECT "
                + "SUM(amount), MAX(date) "
                + "FROM balance "
                + "WHERE type <> ? "
                + "AND mode = ? "
                + "AND id_to=? "
                + "AND date <= ?",
                new String[]{"" + Balance.TYPE_COMPLEX, "" + Balance.MODE_NORMAL, "" + accountID, "" + at.getTimeInMillis()});
        if (cr.moveToFirst()) {
            in = cr.getDouble(0);
            Date d = new Date(cr.getLong(1));
            //if (out > 0 && last.before(d)) {
            //    last = d;
            //}
        }
        cr.close();
        cr = db.getReadableDatabase().rawQuery("SELECT "
                + "SUM(complex.amount), MAX(balance.date) "
                + "FROM balance, complex "
                + "WHERE balance.type = ? "
                + "AND balance.mode = ? "
                + "AND balance.id = complex.id_balance "
                + "AND complex.id_account = ? "
                + "AND balance.date <= ?",
                new String[]{"" + Balance.TYPE_COMPLEX, "" + Balance.MODE_NORMAL, "" + accountID, "" + at.getTimeInMillis()});
        if (cr.moveToFirst()) {
            in += cr.getDouble(0);
            Date d = new Date(cr.getLong(1));
            //if (out > 0 && last.before(d)) {
            //    last = d;
            //}
        }
        cr.close();

        switch (type) {
            case TYPE_NORMAL:
                balance = amount + in - out;
                if (balance >= 0) {
                    balanceDebt = balance;
                    balanceCredit = 0.0;
                } else {
                    balanceCredit = -balance;
                    balanceDebt = 0.0;
                }
                break;

            case TYPE_DEBT:
                balance = amount + out - in;
                if (balance >= 0) {
                    balanceCredit = balance;
                    balanceDebt = 0.0;
                } else {
                    balanceDebt = -balance;
                    balanceCredit = 0.0;
                }
                break;

            case TYPE_CARD:
                balance = amount + in - out;
                if (in >= out) {
                    balanceDebt = in - out;
                    balanceCredit = 0.0;
                } else {
                    balanceCredit = out - in;
                    balanceDebt = 0.0;
                }

                break;
        }

        return new double[]{
                balance,
                balanceDebt,
                balanceCredit
        };
    }

    public void refreshBalance() {

        double[] x = balanceAt(Calendar.getInstance());
        balance = x[0];
        balanceDebt = x[1];
        balanceCredit = x[2];

        /*
        balance = 0.0;
        balanceDebt = 0.0;
        balanceCredit = 0.0;

        double out = 0;
        double in = 0;

        Cursor cr = db.getReadableDatabase().rawQuery("SELECT "
                + "SUM(amount), MAX(date) "
                + "FROM balance "
                + "WHERE mode = ? "
                + "AND id_from = ? ",
                new String[]{"" + Balance.MODE_NORMAL, "" + accountID});
        if (cr.moveToFirst()) {
            out = cr.getDouble(0);
            Date d = new Date(cr.getLong(1));
            if (out > 0 && last.before(d)) {
                last = d;
            }
        }
        cr.close();
        cr = db.getReadableDatabase().rawQuery("SELECT "
                + "SUM(amount), MAX(date) "
                + "FROM balance "
                + "WHERE type <> ? "
                + "AND mode = ? "
                + "AND id_to=?",
                new String[]{"" + Balance.TYPE_COMPLEX, "" + Balance.MODE_NORMAL, "" + accountID});
        if (cr.moveToFirst()) {
            in = cr.getDouble(0);
            Date d = new Date(cr.getLong(1));
            if (out > 0 && last.before(d)) {
                last = d;
            }
        }
        cr.close();
        cr = db.getReadableDatabase().rawQuery("SELECT "
                + "SUM(complex.amount), MAX(balance.date) "
                + "FROM balance, complex "
                + "WHERE balance.type = ? "
                + "AND balance.mode = ? "
                + "AND balance.id = complex.id_balance "
                + "AND complex.id_account = ?",
                new String[]{"" + Balance.TYPE_COMPLEX, "" + Balance.MODE_NORMAL, "" + accountID});
        if (cr.moveToFirst()) {
            in += cr.getDouble(0);
            Date d = new Date(cr.getLong(1));
            if (out > 0 && last.before(d)) {
                last = d;
            }
        }
        cr.close();

        switch (type) {
            case TYPE_NORMAL:
                balance = amount + in - out;
                if (balance >= 0) {
                    balanceDebt = balance;
                    balanceCredit = 0.0;
                } else {
                    balanceCredit = -balance;
                    balanceDebt = 0.0;
                }
                break;

            case TYPE_DEBT:
                balance = amount + out - in;
                if (balance >= 0) {
                    balanceCredit = balance;
                    balanceDebt = 0.0;
                } else {
                    balanceDebt = -balance;
                    balanceCredit = 0.0;
                }
                break;

            case TYPE_CARD:
                balance = amount + in - out;
                if (in >= out) {
                    balanceDebt = in - out;
                    balanceCredit = 0.0;
                } else {
                    balanceCredit = out - in;
                    balanceDebt = 0.0;
                }

                break;
        }
        */
    }

    public boolean linked(Analytics an) {
        if (an == null) {
            return false;
        }

        for (Analytics a : analytics) {
            if (a != null && a.analyticsID == an.analyticsID) {
                return true;
            }
        }
        return false;


        /*
        Cursor cr = db.getReadableDatabase().rawQuery("SELECT COUNT(*) "
                + "FROM relates "
                + "WHERE id_account = ? "
                + "AND id_analytics = ?",
                new String[]{"" + accountID, "" + analytics.analyticsID});
        if (cr.moveToFirst() && cr.getInt(0) > 0) {
            return true;
        }
        return false;
        */
    }

    public void linkVirtual(Analytics an) {
        if (an == null) {
            return;
        }
        for (Analytics a : analytics) {
            if (a.analyticsID == an.analyticsID) {
                return;
            }
        }

        analytics.add(an);
    }

    public void unlinkVirtual(Analytics an) {
        if (an == null) {
            return;
        }
        for (int i = 0; i < analytics.size(); ++i) {
            if (analytics.get(i).analyticsID == an.analyticsID) {
                analytics.remove(i);
                return;
            }
        }
    }


    public void link(Analytics analytics) {
        if (analytics == null) {
            return;
        }
        //if (!linked(analytics)) {
        db.getWritableDatabase().execSQL("INSERT INTO "
                + "relates (id_account, id_analytics) "
                + "VALUES (?,?)",
                new Object[]{accountID, analytics.analyticsID});

        DropboxThread.dataChanged();
        //
        // }
    }

    public void unlink(Analytics analytics) {
        if (analytics == null) {
            return;
        }
        db.getWritableDatabase().execSQL("DELETE FROM relates "
                + "WHERE id_account = ? "
                + "AND id_analytics = ?",
                new Object[]{accountID, analytics.analyticsID});
        DropboxThread.dataChanged();
    }

    @Override
    public String toString() {
        switch (type) {
            case TYPE_NORMAL:
                return "■ " + name;
            case TYPE_DEBT:
                return "□ " + name;
            case TYPE_CARD:
                return "♦ " + name;
        }
        //"●■□♦"
        return name;
    }

}
